﻿using FluentMigrator;
using Mysqlx.Crud;
using MySqlX.XDevAPI;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace CPlatePrintSys.Server.Migrations
{
    [Migration(20251107104434001, "创建CPlate业务数据表")]
    public class Mig20251107104434001_InitCPlateTables: Migration
    {
        public override void Up()
        {
            // 1. 创建 MotherShip 表
            if (!Schema.Table("MotherShip").Exists())
            {
                Create.Table("MotherShip")
                    .WithColumn("Id").AsInt32().Identity().PrimaryKey()
                    .WithColumn("ShipNo").AsString(20).NotNullable();
            }

            // 2. 创建 StreamShip 表
            if (!Schema.Table("StreamShip").Exists())
            {
                Create.Table("StreamShip")
                    .WithColumn("Id").AsInt32().Identity().PrimaryKey()
                    .WithColumn("ShipNo").AsString(20).NotNullable()
                    .WithColumn("MotherShipId").AsInt32().NotNullable();
            }

            // 3. 创建 MotherShip 和 StreamShip 之间的外键
            // 确保两个表都存在再创建外键
            if (Schema.Table("StreamShip").Exists() &&
                !Schema.Table("StreamShip").Constraint("FK_StreamShip_MotherShip").Exists())
            {
                Create.ForeignKey("FK_StreamShip_MotherShip")
                    .FromTable("StreamShip").ForeignColumn("MotherShipId")
                    .ToTable("MotherShip").PrimaryColumn("Id");
            }

            // 4. 创建 GlobalConfig 表
            if (!Schema.Table("GlobalConfig").Exists())
            {
                Create.Table("GlobalConfig")
                    .WithColumn("Id").AsInt32().Identity().PrimaryKey()
                    .WithColumn("ConfigKey").AsString(64).NotNullable()
                    .WithColumn("ConfigValue").AsCustom("TEXT").NotNullable()
                    .WithColumn("Description").AsString(255).Nullable()
                    .WithColumn("UpdatedBy").AsString(64).Nullable();
            }

            // 5. 创建 GlobalConfig 的唯一索引
            if (Schema.Table("GlobalConfig").Exists() &&
                !Schema.Table("GlobalConfig").Index("IX_GlobalConfig_ConfigKey").Exists())
            {
                Create.Index("IX_GlobalConfig_ConfigKey").OnTable("GlobalConfig")
                    .OnColumn("ConfigKey").Ascending()
                    .WithOptions().Unique();
            }

            // 6. 创建 PlateAnnotationInfo 表
            if (!Schema.Table("PlateAnnotationInfo").Exists())
            {
                Create.Table("PlateAnnotationInfo")
                    .WithColumn("Id").AsInt32().Identity().PrimaryKey()
                    .WithColumn("MotherShipNo").AsString(20).Nullable()
                    .WithColumn("StreamShipNo").AsString(20).Nullable()
                    .WithColumn("LotNo").AsString(20).Nullable()
                    .WithColumn("PlateNo").AsString(20).Nullable()
                    .WithColumn("Sequence").AsString(20).Nullable()
                    .WithColumn("CNO").AsString(20).Nullable()
                    .WithColumn("DesignSpecCls").AsString(50).Nullable()
                    .WithColumn("DesignSpecThickness").AsDouble().Nullable()
                    .WithColumn("DesignSpecLength").AsDouble().Nullable()
                    .WithColumn("DesignSpecWidth").AsDouble().Nullable()
                    .WithColumn("SheetConsistentNo").AsString(50).Nullable()
                    .WithColumn("SheetSpecCls").AsString(50).Nullable()
                    .WithColumn("SheetSpecThickness").AsDouble().Nullable()
                    .WithColumn("SheetSpecLength").AsDouble().Nullable()
                    .WithColumn("SheetSpecWidth").AsDouble().Nullable()
                    .WithColumn("LayoutMean").AsInt32().Nullable()
                    .WithColumn("MergedInfo").AsString(100).Nullable()
                    .WithColumn("LayoutInfo").AsCustom("TEXT").Nullable()
                    .WithColumn("PlanUsedDate").AsDateTime().Nullable()
                    .WithColumn("IsAnnotationGenerated").AsBoolean().Nullable()
                    .WithColumn("DesignDrawingPath").AsString(200).Nullable()
                    .WithColumn("AnnotationDrawingPath").AsString(200).Nullable()
                    .WithColumn("SourceType").AsInt32().Nullable();
            }

            // 7. 创建 PlateAnnotationInfo 的所有索引
            if (Schema.Table("PlateAnnotationInfo").Exists())
            {
                if (!Schema.Table("PlateAnnotationInfo").Index("idx_stream_ship_lot_sequence").Exists())
                {
                    Create.Index("idx_stream_ship_lot_sequence").OnTable("PlateAnnotationInfo")
                        .OnColumn("StreamShipNo").Ascending()
                        .OnColumn("LotNo").Ascending()
                        .OnColumn("Sequence").Ascending();
                }

                if (!Schema.Table("PlateAnnotationInfo").Index("idx_mother_ship_no").Exists())
                {
                    Create.Index("idx_mother_ship_no").OnTable("PlateAnnotationInfo")
                        .OnColumn("MotherShipNo").Ascending();
                }

                if (!Schema.Table("PlateAnnotationInfo").Index("idx_plate_no").Exists())
                {
                    Create.Index("idx_plate_no").OnTable("PlateAnnotationInfo")
                        .OnColumn("PlateNo").Ascending();
                }

                if (!Schema.Table("PlateAnnotationInfo").Index("idx_cno").Exists())
                {
                    Create.Index("idx_cno").OnTable("PlateAnnotationInfo")
                        .OnColumn("CNO").Ascending();
                }

                if (!Schema.Table("PlateAnnotationInfo").Index("idx_plan_used_date").Exists())
                {
                    Create.Index("idx_plan_used_date").OnTable("PlateAnnotationInfo")
                        .OnColumn("PlanUsedDate").Ascending();
                }

                if (!Schema.Table("PlateAnnotationInfo").Index("idx_source_type").Exists())
                {
                    Create.Index("idx_source_type").OnTable("PlateAnnotationInfo")
                        .OnColumn("SourceType").Ascending();
                }

                if (!Schema.Table("PlateAnnotationInfo").Index("idx_layout_mean").Exists())
                {
                    Create.Index("idx_layout_mean").OnTable("PlateAnnotationInfo")
                        .OnColumn("LayoutMean").Ascending();
                }

                if (!Schema.Table("PlateAnnotationInfo").Index("idx_is_annotation_generated").Exists())
                {
                    Create.Index("idx_is_annotation_generated").OnTable("PlateAnnotationInfo")
                        .OnColumn("IsAnnotationGenerated").Ascending();
                }
            }
        }

        public override void Down()
        {
            // 按照与创建时相反的顺序删除
            // 同样添加存在性检查，以防止在回滚时出错

            // 1. 删除 PlateAnnotationInfo
            if (Schema.Table("PlateAnnotationInfo").Exists())
            {
                Delete.Table("PlateAnnotationInfo");
            }

            // 2. 删除 GlobalConfig
            if (Schema.Table("GlobalConfig").Exists())
            {
                Delete.Table("GlobalConfig");
            }

            // 3. 删除外键
            if (Schema.Table("StreamShip").Constraint("FK_StreamShip_MotherShip").Exists())
            {
                Delete.ForeignKey("FK_StreamShip_MotherShip").OnTable("StreamShip");
            }

            // 4. 删除 StreamShip
            if (Schema.Table("StreamShip").Exists())
            {
                Delete.Table("StreamShip");
            }

            // 5. 删除 MotherShip
            if (Schema.Table("MotherShip").Exists())
            {
                Delete.Table("MotherShip");
            }
        }
    }
}